<?php

/**
 * class purchaseOrders
 *
 * Description for class purchaseOrders
 *
 * @author:peter
*/
Zend_Loader::loadClass("BaseDBRows");
Zend_Loader::loadClass("purchaseOrder");
class purchaseOrders extends BaseDBRows {

	protected function setUp()
	{
		$this->_tableName = purchaseOrder::_tablename;	
	}
	
	public function getOrderByOrg($org_id,$blanketpo=0,$param=array()){
		
		//print_r($param);
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("OrganizationOrganism");
		Zend_Loader::loadClass("Organism");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("ShipmentDetail");
		
		$query = new QueryCreator();
		$query->addSelect("org.name as supplier,po.date_order,blanket_po_no,sum(podetail.order_quantity * podetail.order_amount) as totalAmount,po.po_no,po.po_id,po.status,podetail.supplier_org_id,po.po_code");
		//$query->addSelect(" podetail.order_quantity - ifnull((select sum(ship_quantity) from ". ShipmentDetail::_tablename ." shipment where shipment.po_line_no = podetail.po_detail_id ),0) as balance");
		$query->addFrom($this->_tableName . "  po ");
		$query->addJoin(" INNER JOIN ". purchaseOrderDetail::_tablename . " podetail on po.po_id = podetail.po_id " );
		$query->addJoin(" LEFT JOIN ". Organization::_tablename . " org on org.org_id = podetail.supplier_org_id");
		$query->addJoin(" LEFT JOIN ". Address::_tablename . " address on address.address_id = org.primary_address_id ");
		if($org_id)
			$query->addWhere(" po.org_id = ". $org_id);
			
		if($blanketpo){
			$query->addWhere(" blanket_po_no = 1");
		}
		
		if(isset($param['supplier']) &&  $param['supplier'] > 0 )
			$query->addWhere(" podetail.supplier_org_id = ". $param['supplier'] );
			
		if(isset($param['country']) && strlen($param['country']) > 0 )
			$query->addWhere(" address.country_code = '". $param['country'] . "'" );
			
		if(strlen($param['date_from']) && strlen($param['date_to']) )
			$query->addWhere(" po.date_order between '". $param['date_from'] . "' and '". $param['date_to'] ."'");
			
		$query->addWhere(" po.po_no not like 'TMP%'");
		//$query->addHaving(" balance > 0 ");
		
		$query->addGroupBy(" po.po_id,podetail.supplier_org_id ");
		$query->addOrderBy(" po.po_id desc ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		
		return $result;
	}
	
	public function getOrdersDetailsByOrg($org_id,$poid,$supplier_org_id=0,$arrayonly=0,$balanceonly=0){
		
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("OrganizationOrganism");
		Zend_Loader::loadClass("Organism");
		
		$query = new QueryCreator();
		$query->addSelect("po.*,podetail.*, org.name as supplier,orgorg.organism_id,orgorg.base_price_sale,organism.name, organism.moid, organism.scientific_name,organism.moid,replace(organism.scientific_name,' ','-') as image");
		$query->addFrom($this->_tableName . "  po ");
		$query->addJoin(" LEFT JOIN ". purchaseOrderDetail::_tablename . " podetail on po.po_id = podetail.po_id " );
		$query->addJoin(" LEFT JOIN ". Organization::_tablename . " org on org.org_id = podetail.supplier_org_id");
		$query->addJoin(" LEFT JOIN ". OrganizationOrganism::_tablename . " orgorg on orgorg.org_organism_id = podetail.org_organism_id ");
		$query->addJoin(" LEFT JOIN ". Organism::_tablename . " organism on organism.organism_id = orgorg.organism_id");
		$query->addWhere(" po.org_id = ". $org_id);
		$query->addWhere(" po.po_id = ". $poid);
		
		if($balanceonly) {
			Zend_Loader::loadClass("Receipt");
			$query->addSelect(" podetail.order_quantity - ifnull((select sum(rcpt.received_quantity - rcpt.doa_quantity - rcpt.reject_quantity) from ". Receipt::_tablename  ." rcpt where rcpt.reference_id = podetail.po_detail_id and rcpt.source = 'PO' ),0) as balance  ");
			$query->addHaving(' balance > 0 ');
		}
		
		if($supplier_org_id) {
			$query->addWhere(" podetail.supplier_org_id = ". $supplier_org_id );
		}
		//print $query->createSQL();
		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());
		$data = array();
		if($arrayonly == 0){
			//print_r($result);
			foreach($result as $key => $value){
				$item = array();
				$item = array('moid'=>$value['moid'], 'organism_id'=>$value['organism_id'],'name'=>$value['name'],'scientific_name'=>$value['scientific_name'],'image'=>$value['image'],'price'=>$value['order_amount'],'org_organism_id'=>$value['org_organism_id'],'currency_mark'=>$value['currency_mark']);

				if(!isset($data[$value['supplier_org_id']])) {
					$data[$value['supplier_org_id']]['organismItem'][$value['organism_id']] = $item;
					$data[$value['supplier_org_id']]['supplier'] 							= $value['supplier'];
					$data[$value['supplier_org_id']]['blanketpo'] 							= $value['blanket_po_no'];
					$data[$value['supplier_org_id']]['po_no'] 								= $value['po_no'];
					
				} else {
					if (!isset($data[$value['supplier_org_id']]['organismItem'][$value['organism_id']])) {
						$data[$value['supplier_org_id']]['organismItem'][$value['organism_id']] =  $item;
					}
				}
				
				if(isset( $data[$value['supplier_org_id']]['totalorder'] )) {
					$data[$value['supplier_org_id']]['totalorder'] = $data[$value['supplier_org_id']]['totalorder'] + $value['order_quantity'];
				} else {
					$data[$value['supplier_org_id']]['totalorder'] = $value['order_quantity'];
				}
				$data[$value['supplier_org_id']]['organismItem'][$value['organism_id']]['order'][$value['size_code'].'_'.$value['gender']] = $value['order_quantity'];
				
				
			}
			return $data;
		} else {
			return $result;
		}
		
	}
	
	public function getSupplierByOrg($org_id,$country_code = ''){
		
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("Address");
		
		
		$query = new QueryCreator();
		$query->addSelect("org.name as supplier,org.org_id");
		$query->addFrom($this->_tableName . "  po ");
		$query->addJoin(" LEFT JOIN ". purchaseOrderDetail::_tablename . " podetail on po.po_id = podetail.po_id " );
		$query->addJoin(" LEFT JOIN ". Organization::_tablename . " org on org.org_id = podetail.supplier_org_id");
		$query->addJoin(" LEFT JOIN ". Address::_tablename . " address on address.address_id = org.primary_address_id");
		$query->addWhere(" po.org_id = ". $org_id);
		if(strlen($country_code) > 0 )
			$query->addWhere("country_code = '". $country_code ."'");
			
		$query->addGroupBy("org.org_id");
		
		$db = DBCon::instance();
		
		$result = $db->executeQuery($query->createSQL());
		
		return $result;		
	}
	
	public function getCustomerByOrg($org_id) {
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
				
		$query = new QueryCreator();
		$query->addSelect("org.name as supplier,po.org_id");
		$query->addFrom($this->_tableName . "  po ");
		$query->addJoin(" LEFT JOIN ". purchaseOrderDetail::_tablename . " podetail on po.po_id = podetail.po_id " );
		$query->addJoin(" LEFT JOIN ". Organization::_tablename . " org on org.org_id = po.org_id");
		$query->addWhere(" podetail.supplier_org_id = ". $org_id);
		
		$query->addGroupBy("org.name");
		//print $query->createSQL();
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		
		return $result;		
	}
	
	public function getSupplierPoByOrg($org_id,$supplier_org_id,$balanceonly = 1){
		
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		
		if (empty($supplier_org_id)) {
			// found this error if call to retrieve for dashbord
			$supplier_org_id_criteria = " <> 0 ";
		} else {
			$supplier_org_id_criteria = " = ". $supplier_org_id;
		}
		$query = new QueryCreator();
		$query->addSelect("po.po_no,po.po_id");
		$query->addFrom($this->_tableName . "  po ");
		$query->addJoin(" LEFT JOIN ". purchaseOrderDetail::_tablename . " podetail on po.po_id = podetail.po_id " );
		$query->addJoin(" LEFT JOIN ". Organization::_tablename . " org on org.org_id = podetail.supplier_org_id");
		$query->addWhere(" po.org_id = ". $org_id . " and podetail.supplier_org_id". $supplier_org_id_criteria );
		$query->addWhere(" po.po_no not like 'TMP%'");
		
		if($balanceonly) {
			Zend_Loader::loadClass("Receipt");
			$query->addSelect(" podetail.order_quantity - ifnull((select sum(rcpt.received_quantity - rcpt.doa_quantity - rcpt.reject_quantity) from ". Receipt::_tablename  ." rcpt where rcpt.reference_id = podetail.po_detail_id and rcpt.source = 'PO' ),0) as balance  ");
			$query->addHaving(' balance > 0 ');
		}
		
		$query->addGroupBy(" po.po_id ");
		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());
		
		return $result;		
	}
	
	public function getCustomerPoByOrg($org_id,$customer_org_id) {
	
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("ShipmentDetail");
		
		
		$query = new QueryCreator();
		$query->addSelect("po.po_no,po.po_id");
		$query->addSelect(" sum(podetail.order_quantity) - ifnull((select sum(ship_quantity) from ". ShipmentDetail::_tablename ." shipment where shipment.po_line_no = podetail.po_detail_id ),0) as balance");
		$query->addFrom($this->_tableName . "  po ");
		$query->addJoin(" LEFT JOIN ". purchaseOrderDetail::_tablename . " podetail on po.po_id = podetail.po_id " );
		$query->addJoin(" LEFT JOIN ". Organization::_tablename . " org on org.org_id = po.org_id");
		$query->addWhere(" podetail.supplier_org_id = ". $org_id);
		$query->addWhere(" po.org_id = ". $customer_org_id );
		
		$query->addGroupBy("podetail.supplier_org_id,po.po_id");
		$query->addHaving(" balance > 0");
		
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		
		return $result;		
	}
	
	public function getOrderExported($supplier_org_id){
		
		Zend_Loader::loadClass("PurchaseOrderDetail");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query1 = new QueryCreator();
		
		$query->addSelect(" a.* ");
		$query->addFrom($this->_tableName . " a ");
		$query->addJoin(" LEFT JOIN ". PurchaseOrderDetail::_tablename . " b on a.po_id = b.po_id ");
		$query->addWhere(" supplier_org_id = ". $supplier_org_id ." and exported = 0 " ) ;
		
		$db = DBCon::instance();
		
		$result = $db->executeQuery($query->createSQL());
		$data = array();
		foreach($result as $key => $value) {
			
			$data['head'][$key] = $value;
			$query1->clear();
			$query1->addSelect(" * ");
			$query1->addFrom(PurchaseOrderDetail::_tablename);
			$query1->addWhere(" po_id = ". $value['po_id']);
			
			$result1 = $db->executeQuery($query1->createSQL());
			
			$data['detail'][] = $result1[0];
			
			
		}
		
		return $data;
		
	
		
	}
}

?>